﻿/***********************************************************************************************************************************
 *    Name: Logins_Delete.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Deletes a login and any database users with the same SID as the login.
**/
DECLARE
  @login_sid		VARBINARY(85),
  @login_name		SYSNAME,
  @user_name		SYSNAME,
  @database_name	SYSNAME,
  @template			NVARCHAR(MAX),
  @tsql				NVARCHAR(MAX),
  @errors			INT;

SET @login_name= N'BBAAviation\SE Developers';

SELECT @login_sid= p.sid FROM sys.server_principals p WHERE p.name = @login_name;
IF @login_sid IS Null GOTO EndScript;
SET @template= N'USE [<database_name>];
	DECLARE @drop NVARCHAR(MAX); SET @drop= N'''';
	SELECT @drop= @drop+ N''DROP SCHEMA ['' + s.name + N''];'' FROM sys.schemas s INNER JOIN sys.database_principals p ON (s.principal_id = p.principal_id) WHERE p.sid= ' + sys.fn_varbintohexstr(@login_sid) + N';
	SELECT @drop= @drop+ N''DROP USER   ['' + p.name + N''];'' FROM sys.database_principals p WHERE p.sid= ' + sys.fn_varbintohexstr(@login_sid) + N';
	IF 0 < LEN(@drop) EXECUTE (@drop);';

DECLARE listDBs CURSOR LOCAL FAST_FORWARD FOR SELECT database_name FROM DBAdmin.DBList(DEFAULT);
OPEN listDBs;
SET @errors= 0;
WHILE (0=0) BEGIN;
	FETCH NEXT FROM listDBs INTO @database_name;
	IF @@FETCH_STATUS <> 0 BREAK;
	PRINT @database_name;
	SET @tsql= REPLACE(@template, N'<database_name>', @database_name);
	EXECUTE (@tsql);
	IF @@ERROR <> 0 SELECT @errors= @errors+1;
END;
CLOSE listDBs;
DEALLOCATE listDBs;

SET @tsql= N'BEGIN TRY DROP LOGIN [' + @login_name + N']; END TRY BEGIN CATCH END CATCH';
IF @errors = 0 EXECUTE(@tsql);
ELSE PRINT N'Database Users Remain.';

EndScript:
GO